package cn.doitedu.day07

import org.apache.spark.sql.SparkSession

object T21_SQLShopIncome {

  def main(args: Array[String]): Unit = {
    val days = 3
    val spark = SparkSession.builder().appName(this.getClass.getSimpleName)
      .master("local[4]")
      .getOrCreate()

    //创建DataFrame
    val df = spark.read
      .csv("data/shop.txt")
      .toDF("id", "dt", "money")


    df.createTempView("v_shop")

    val res = spark.sql(
      s"""
        |select
        |    id,
        |    mth,
        |    money,
        |    sum(money) over(partition by id order by mth range between unbounded preceding and current row) sum_money
        |from
        |(
        |    select
        |        id,
        |        mth,
        |        sum(money) money
        |    from
        |    (
        |        select
        |            id,
        |            substr(dt, 0, 7) mth,
        |            money
        |        from
        |            v_shop
        |    )
        |    group by id, mth
        |)
        |
        |""".stripMargin)

    res.show()



  }

}
